﻿using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using SSTestAPI;

namespace Data_Storages
{
    class DSDB : IDataStorage
    {
        private string ConnectionString { get; set; }
        //@"Data Source=TORO\SQLEXPRESS;Initial Catalog=TestingDB;Integrated Security=True;Pooling=False"
        private readonly SqlConnection _sqlConnection;
        
        public DSDB(string connectionString)
        {
            ConnectionString = connectionString;
            _sqlConnection = new SqlConnection(connectionString);
        }

        public ArrayList Load()
        {
            ArrayList plants;
            try
            {
                _sqlConnection.Open();
                var cmd = _sqlConnection.CreateCommand();
                cmd.CommandText = "SELECT COUNT(*) AS Size FROM Plants";
                var count = (int)cmd.ExecuteScalar();
                plants = new ArrayList(count);
                cmd.CommandText = "SELECT " + 
                    "Plants.OrderId, Plants.Branch, Plants.Lifetime, " + 
                    "Flowers.Symmetric, Flowers.LeafsQuantity " + 
                    "FROM Plants INNER JOIN Flowers ON Plants.Id = Flowers.PlantId " + 
                    "ORDER BY Plants.OrderId";
                var dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    var i = dataReader.GetInt32(0);
                    plants[i - 1] = new Flower(dataReader.GetString(1), dataReader.GetBoolean(3), 
                        dataReader.GetInt32(4), dataReader.GetInt32(2));
                }
                dataReader.Close();
                cmd.CommandText = "SELECT " + 
                    "Plants.OrderId, Plants.Branch, Plants.Lifetime, " + 
                    "Trees.GroupName, Trees.Fruit " + 
                    "FROM Plants INNER JOIN Trees ON Plants.Id = Trees.PlantId " + 
                    "ORDER BY Plants.OrderId";
                dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    var i = dataReader.GetInt32(0);
                    plants[i - 1] = new Tree(dataReader.GetString(1), dataReader.GetString(3), 
                        dataReader.GetBoolean(4), dataReader.GetInt32(2));
                }
                dataReader.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (_sqlConnection.State == ConnectionState.Open)
                {
                    _sqlConnection.Close();
                }
            }
            return plants;
        }

        public void Save(ArrayList data)
        {
            try
            {
                _sqlConnection.Open();
                var cmd = _sqlConnection.CreateCommand();
                for (var i = 0; i < data.Count; i++)
                {
                    cmd.CommandText = "Insert into Plants(Branch, Lifetime, OrderId) " + 
                        "Values(@branch, @lifetime, @orderid)";
                    cmd.Parameters.Add("@branch", SqlDbType.VarChar);
                    cmd.Parameters["@branch"].Value = ((Plant)data[i]).Branch;
                    cmd.Parameters.Add("@lifetime", SqlDbType.Int);
                    cmd.Parameters["@lifetime"].Value = ((Plant)data[i]).getLifetime();
                    cmd.Parameters.Add("@orderid", SqlDbType.Int);
                    cmd.Parameters["@orderid"].Value = i;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT Id FROM Plants WHERE (OrderId = @i)";
                    cmd.Parameters.Add("@i", SqlDbType.Int);
                    cmd.Parameters["@i"].Value = i;
                    var id = (int)cmd.ExecuteScalar();
                    if (data[i] is Tree)
                    {
                        cmd.CommandText = "Insert into lower(Symmetric, LeafsQuantity, PlantId) " + 
                            "Values(@symmetric, @leafsquantity, @plantid)";
                        cmd.Parameters.Add("@symmetric", SqlDbType.Bit);
                        cmd.Parameters["@symmetric"].Value = ((Flower)data[i]).Symmetric;
                        cmd.Parameters.Add("@leafsquantity", SqlDbType.Int);
                        cmd.Parameters["@leafsquantity"].Value = ((Flower)data[i]).LeafsQuantity;
                        cmd.Parameters.Add("@plantid", SqlDbType.Int);
                        cmd.Parameters["@plantid"].Value = id;
                        cmd.ExecuteNonQuery();
                    }
                    else if (data[i] is Flower)
                    {
                        cmd.CommandText = "Insert into Trees(GroupName, Fruit, PlantId) " + 
                            "Values(@groupname, @fruit, @plantid)";
                        cmd.Parameters.Add("@groupname", SqlDbType.VarChar);
                        cmd.Parameters["@groupname"].Value = ((Tree)data[i]).Group;
                        cmd.Parameters.Add("@fruit", SqlDbType.Bit);
                        cmd.Parameters["@fruit"].Value = ((Tree)data[i]).Fruit;
                        cmd.Parameters.Add("@plantid", SqlDbType.Int);
                        cmd.Parameters["@plantid"].Value = id;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (_sqlConnection.State == ConnectionState.Open)
                {
                    _sqlConnection.Close();
                }
            }
        }
    }
}
